﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using NPOI.SS.UserModel;
using NPOI.SS.Util;
using NPOI.HSSF.UserModel;
using System.IO;

namespace ScoreMS.BLL
{
    /// <summary>
    /// 输入输出处理类，事务逻辑
    /// </summary>
    public class IO
    {
        /// <summary>
        /// 批量导入考试分数
        /// </summary>
        /// <param name="file">FileStream</param>
        /// <param name="course_id">课程ID</param>
        /// <param name="ex_id">试卷ID</param>
        /// <param name="studentCount">学生数</param>
        /// <returns></returns>
        public static bool InputScore(FileStream file, string course_id, int ex_id, int studentCount)
        {
            try
            {
                int problemCount = BLL.Examination_paparBS.GetProblemCount(ex_id);

                HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
                ISheet sheet1 = hssfworkbook.GetSheetAt(hssfworkbook.ActiveSheetIndex);

                for (int i = 1; i <= studentCount; i++)
                {
                    var row = sheet1.GetRow(i);
                    var model = new Model.Student()
                    {
                        student_id = getValueAsStr(row.GetCell(0)),
                        student_name = getValueAsStr(row.GetCell(1)),
                        student_sex = getValueAsStr(row.GetCell(2)),
                        class_id = getValueAsStr(row.GetCell(3)),
                    };
                    bool ret;
                    ret = BLL.StudentBS.AddStudent(model);
                    ret = BLL.StudentBS.AddCourse(model.student_id, course_id);
                    var scoreList = new List<int>();
                    for (int j = 4; j < 4 + problemCount; j++)
                    {
                        scoreList.Add(int.Parse(getValueAsStr(row.GetCell(j))));
                    }
                    ret = BLL.Examination_paparBS.AddScore(ex_id, model.student_id, scoreList);
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 批量导入平时分数
        /// </summary>
        /// <param name="file">FileStream</param>
        /// <param name="course_id">课程ID</param>
        /// <param name="ex_id">试卷ID</param>
        /// <param name="studentCount">学生数</param>
        /// <returns></returns>
        public static bool InputScoreOther(FileStream file, string course_id, int studentCount)
        {
            try
            {
                HSSFWorkbook hssfworkbook = new HSSFWorkbook(file);
                ISheet sheet1 = hssfworkbook.GetSheetAt(hssfworkbook.ActiveSheetIndex);

                for (int i = 1; i <= studentCount; i++)
                {
                    var row = sheet1.GetRow(i);
                    var model = new Model.Student()
                    {
                        student_id = getValueAsStr(row.GetCell(0)),
                        student_name = getValueAsStr(row.GetCell(1)),
                        student_sex = getValueAsStr(row.GetCell(2)),
                        class_id = getValueAsStr(row.GetCell(3)),
                    };
                    BLL.StudentBS.AddStudent(model);
                    BLL.StudentBS.AddCourse(model.student_id, course_id);

                    BLL.Score_otherBS.AddScore(new Model.Score_other() {
                        course_id = course_id,
                        score_other_attendance = int.Parse(getValueAsStr(row.GetCell(4))),
                        score_other_homework = int.Parse(getValueAsStr(row.GetCell(5))),
                        score_other_task = int.Parse(getValueAsStr(row.GetCell(6))),
                        student_id = model.student_id
                    });
                }
                return true;
            }
            catch
            {
                return false;
            }
        }

        /// <summary>
        /// 按班级开设的课程，填写详细成绩表
        /// </summary>
        /// <param name="sheet"></param>
        /// <param name="class_id"></param>
        /// <param name="PageLength"></param>
        public static bool Generate(HSSFWorkbook hssfworkbook, ISheet sheet, string class_id, string course_name, string teacher_name,
            int course_points, List<Model.Score_final> list,
            int PageTotalCount = 25)
        {
            try
            {
                var row10 = sheet.GetRow(1).GetCell(0);
                var row20 = sheet.GetRow(2).GetCell(0);
                row10.SetCellValue(row10.StringCellValue.Replace("{class_id}", class_id));
                row10.SetCellValue(row10.StringCellValue.Replace("{course_name}", course_name));
                row20.SetCellValue(row20.StringCellValue.Replace("{teacher_name}", teacher_name));
                row20.SetCellValue(row20.StringCellValue.Replace("{course_points}", course_points.ToString()));

                //根据学生数，确定页数
                var pageLength = PageTotalCount + 6;
                var studentCount = list.Count;
                int pageCount = studentCount / (PageTotalCount * 2) + (studentCount % (PageTotalCount * 2) == 0 ? 0 : 1);
                for (int i = 1; i < pageCount; i++)
                {
                    for (int j = 0; j < pageLength; j++)
                    {
                        CopyRow(hssfworkbook, sheet, j, j + i * pageLength);
                    }
                }
                var itor = list.GetEnumerator();

                //var model = new Model.Score_final()
                //{
                //    score_exam = 100,
                //    //score_final = 90,
                //    score_other = 90,
                //    student_id = "13101249",
                //    student_name = "李寒",
                //    student_sex = "男",
                //};

                bool end = false;
                for (int k = 0; k < pageCount; k++)
                {
                    if (end) break;
                    for (int col = 0; col < 2; col++)
                    {
                        if (end) break;
                        for (int row = 5; row < 5 + PageTotalCount; row++)
                        {
                            int colbase = col == 0 ? 0 : 6;
                            var rowid = row + pageLength * k;

                            if (!itor.MoveNext())
                            {
                                end = true;
                                break;
                            }
                            var model = itor.Current;
                            sheet.GetRow(rowid).GetCell(0 + colbase).SetCellValue(model.student_id);
                            sheet.GetRow(rowid).GetCell(1 + colbase).SetCellValue(model.student_name);
                            sheet.GetRow(rowid).GetCell(2 + colbase).SetCellValue(model.student_sex);
                            sheet.GetRow(rowid).GetCell(3 + colbase).SetCellValue(model.score_other);
                            sheet.GetRow(rowid).GetCell(4 + colbase).SetCellValue(model.score_exam);
                        }
                    }
                }
                FileStream outputFile = new FileStream(@"Output/"
                    + class_id + "_" + course_name + "_" + DateTime.Now.ToString("HHmmss") + ".xls"
                    , FileMode.Create);
                hssfworkbook.Write(outputFile);
                outputFile.Close();
                return true;
            }
            catch
            {
                return false;
            }
        }

        static string getValueAsStr(ICell cell)
        {
            switch (cell.CellType)
            {
                case CellType.NUMERIC:
                    return cell.NumericCellValue.ToString();
                case CellType.STRING:
                    return cell.StringCellValue;
                default:
                    return string.Empty;
            }
        }

        /// <summary>
        /// HSSFRow Copy Command
        ///
        /// Description:  Inserts a existing row into a new row, will automatically push down
        ///               any existing rows.  Copy is done cell by cell and supports, and the
        ///               command tries to copy all properties available (style, merged cells, values, etc...)
        /// </summary>
        private static void CopyRow(HSSFWorkbook workbook, ISheet worksheet, int sourceRowNum, int destinationRowNum)
        {
            // Get the source / new row
            IRow newRow = worksheet.GetRow(destinationRowNum);
            IRow sourceRow = worksheet.GetRow(sourceRowNum);

            // If the row exist in destination, push down all rows by 1 else create a new row
            if (newRow != null)
            {
                worksheet.ShiftRows(destinationRowNum, worksheet.LastRowNum, 1);
            }
            else
            {
                newRow = worksheet.CreateRow(destinationRowNum);
            }

            // Loop through source columns to add to new row
            for (int i = 0; i < sourceRow.LastCellNum; i++)
            {
                // Grab a copy of the old/new cell
                ICell oldCell = sourceRow.GetCell(i);
                ICell newCell = newRow.CreateCell(i);

                // If the old cell is null jump to next cell
                if (oldCell == null)
                {
                    newCell = null;
                    continue;
                }

                // Copy style from old cell and apply to new cell
                ICellStyle newCellStyle = workbook.CreateCellStyle();
                newCellStyle.CloneStyleFrom(oldCell.CellStyle); ;
                newCell.CellStyle = newCellStyle;

                // If there is a cell comment, copy
                if (newCell.CellComment != null) newCell.CellComment = oldCell.CellComment;

                // If there is a cell hyperlink, copy
                if (oldCell.Hyperlink != null) newCell.Hyperlink = oldCell.Hyperlink;

                // Set the cell data type
                newCell.SetCellType(oldCell.CellType);

                // Set the cell data value
                switch (oldCell.CellType)
                {
                    case CellType.BLANK:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                    case CellType.BOOLEAN:
                        newCell.SetCellValue(oldCell.BooleanCellValue);
                        break;
                    case CellType.ERROR:
                        newCell.SetCellErrorValue(oldCell.ErrorCellValue);
                        break;
                    case CellType.FORMULA:
                        newCell.SetCellFormula(oldCell.CellFormula);
                        break;
                    case CellType.NUMERIC:
                        newCell.SetCellValue(oldCell.NumericCellValue);
                        break;
                    case CellType.STRING:
                        newCell.SetCellValue(oldCell.RichStringCellValue);
                        break;
                    case CellType.Unknown:
                        newCell.SetCellValue(oldCell.StringCellValue);
                        break;
                }
            }

            // If there are are any merged regions in the source row, copy to new row
            for (int i = 0; i < worksheet.NumMergedRegions; i++)
            {
                CellRangeAddress cellRangeAddress = worksheet.GetMergedRegion(i);
                if (cellRangeAddress.FirstRow == sourceRow.RowNum)
                {
                    CellRangeAddress newCellRangeAddress = new CellRangeAddress(newRow.RowNum,
                                                                                (newRow.RowNum +
                                                                                 (cellRangeAddress.FirstRow -
                                                                                  cellRangeAddress.LastRow)),
                                                                                cellRangeAddress.FirstColumn,
                                                                                cellRangeAddress.LastColumn);
                    worksheet.AddMergedRegion(newCellRangeAddress);
                }
            }

        }
    }
}
